package com.code.entity.generator;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import com.code.constant.BooleanEnum;
import com.code.constant.SqlConstant;
import com.code.utils.ExcelUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

@Slf4j
public class SqlGenerator {

    private static String DEFAULT_OUTPUT_DIR = "";
    private static String PREFIX_DIR = File.separator + "script" + File.separator;
    private static String DEFAULT_EXCEL = "";
    private static String DEFAULT_MYSQL_FILE = "";
    private static String DEFAULT_ORACLE_DIR = "";
    private static String DEFAULT_ORACLE_FILE = "";

    private static void setDir() {
        log.info("=======根目录 " + DEFAULT_OUTPUT_DIR);
        DEFAULT_OUTPUT_DIR = DEFAULT_OUTPUT_DIR + PREFIX_DIR;
        log.info("=======文件根目录 " + DEFAULT_OUTPUT_DIR);
        DEFAULT_EXCEL = DEFAULT_OUTPUT_DIR + "ai_gateway建表.xls";
        DEFAULT_MYSQL_FILE = DEFAULT_OUTPUT_DIR + "2.BatchTable.sql";
        DEFAULT_ORACLE_DIR = DEFAULT_OUTPUT_DIR + "oracle" + File.separator;
        DEFAULT_ORACLE_FILE = DEFAULT_ORACLE_DIR + "2.BatchTable.sql";
    }

    public static void generate(String version) throws IOException {
        DEFAULT_OUTPUT_DIR = System.getProperty("user.dir");
        //获取上级目录
        log.info("=======当前目录 " + DEFAULT_OUTPUT_DIR);
        DEFAULT_OUTPUT_DIR = DEFAULT_OUTPUT_DIR.substring(0, DEFAULT_OUTPUT_DIR.lastIndexOf(File.separator));
        setDir();

        // 如果已存在,删除旧文件
        File file = new File(DEFAULT_MYSQL_FILE);
        if (file.exists()) {
            file.delete();
        }
        file = new File(DEFAULT_MYSQL_FILE);
        file.createNewFile();

        File oracleFile = new File(DEFAULT_ORACLE_FILE);
        if (oracleFile.exists()) {
            oracleFile.delete();
        }
        oracleFile = new File(DEFAULT_ORACLE_FILE);
        oracleFile.createNewFile();

        createSqlFile(version);


    }

    private static void createSqlFile(String version) {
        try {
            File file = new File(DEFAULT_MYSQL_FILE);
            File oracleFile = new File(DEFAULT_ORACLE_FILE);
            //设置输出文件的编码为utf-8
            PrintWriter pfp = new PrintWriter(file, "UTF-8");
            pfp.print(SqlConstant.mysqlUseDb);

            PrintWriter oraclePfp = new PrintWriter(oracleFile, "UTF-8");


            List<Table> tables = importSheets(new File(DEFAULT_EXCEL), version);
            int tableCount = 0;
            int histiryCount = 0;
            for (Table table : tables) {
                tableCount++;
                if (table.getColumns().stream().anyMatch(c -> StringUtils.isNotBlank(c.getWithHistory()) && c.getWithHistory().contains("是"))) {
                    histiryCount++;
                }
                TableGenerator generator = new TableGenerator();
                generator.setTable(table);

                pfp.print(generator.getMysqlCreateTableSql());
                pfp.flush();

                oraclePfp.print(generator.getOracleCreateTableSql());
                oraclePfp.flush();

            }
            log.info("=======================生成业务表" + tableCount + "张");
            log.info("=======================生成历史表" + histiryCount + "张");
            log.info("=======================共生成表" + (tableCount + histiryCount) + "张");
            pfp.close();
            oraclePfp.close();

        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    private static List<Table> importSheets(File file, String version) {
        List<Table> tables = new ArrayList<>();
        try {
            // 根据file得到Workbook,主要是要根据这个对象获取,传过来的excel有几个sheet页
            Workbook workBook = ExcelUtil.getWorkBook(file);
            StringBuilder sb = new StringBuilder();
            ImportParams params = new ImportParams();
            // 循环工作表Sheet
            for (int numSheet = 3; numSheet < workBook.getNumberOfSheets(); numSheet++) {
                // 表头在第几行
                params.setTitleRows(0);
                // 距离表头中间有几行不要的数据
                params.setStartRows(0);
                // 第几个sheet页
                params.setStartSheetIndex(numSheet);

                ExcelImportResult<Column> result = ExcelImportUtil.importExcelMore(new FileInputStream(file),
                        Column.class, params);
                // 校验是否合格
                if (result.isVerifyFail()) {
                    // 不合格的数据
                    List<Column> errorList = result.getList();
                    // 拼凑错误信息,自定义
                    for (int i = 0; i < errorList.size(); i++) {
                        ExcelUtil.getWrongInfo(sb, errorList, i, errorList.get(i), "name", "excel信息不合法");
                    }
                }
                // 合格的数据
                List<Column> successList = result.getList();
                //数据初步加工
                for (Column column : successList) {
                    if (StringUtils.isNotBlank(column.getDataLength()) && column.getDataLength().endsWith(",0")) {
                        column.setDataLength(column.getDataLength().replace(",0", ""));
                    }
                    if (StringUtils.isNoneBlank(column.getTableName())) {
                        column.setTableName(column.getTableName().trim());
                    }
                    if (StringUtils.isNoneBlank(column.getColumnName())) {
                        column.setColumnName(column.getColumnName().trim());
                    }
                }
                tables.add(setColumns(successList, workBook.getSheetName(numSheet), version));
                log.info("开始生成表{}", workBook.getSheetName(numSheet));
            }

        } catch (Exception e) {
            log.error("导入表结构EXCEL失败:{}", e.getCause(), e);
        }
        return tables;
    }

    //设置列数据
    private static Table setColumns(List<Column> columns, String tableName, String version) {
        Map<Integer, List<Column>> uniqueIndexGroup = new HashMap<>();
        Map<String, List<Column>> indexGroup = new HashMap<>();
        Table table = new Table();
        table.setTableName(columns.get(0).getTableName());
        table.setTableComment(tableName);
        Iterator<Column> it = columns.iterator();
        while (it.hasNext()) {
            Column data = it.next();
            //忽略更高版本字段
            if (data.getVersion() != null && isNumber(data.getVersion())
                    && new BigDecimal(data.getVersion()).compareTo(new BigDecimal(version)) > 0) {
                it.remove();
                continue;
            }
            if (BooleanEnum.YES.getExcelData().equals(data.getNullState())) {
                data.setNullState(SqlConstant.nullState_not_null);
            } else {
                data.setNullState(SqlConstant.nullState_default_null);
            }
            data.setExtend(BooleanEnum.getBoolean(data.getExtendFlag() == null ? "" : data.getExtendFlag()));

            if (BooleanEnum.YES.getExcelData().equals(data.getIndex())) {
                addUniqueIndexByGroupNo(0, uniqueIndexGroup, data);
            } else if (StringUtils.isNotBlank(data.getIndex())) {
                String index = data.getIndex().replaceAll("，", ",");
                for (String i : index.split(",")) {
                    //唯一索引
                    if (isNumber(i)) {
                        addUniqueIndexByGroupNo(Integer.parseInt(i), uniqueIndexGroup, data);
                    } else if (i.substring(0, 1).equalsIgnoreCase("i")) {
                        //普通索引
                        addIndexByGroupNo(i, indexGroup, data);
                    }
                }
            }
        }
        table.setColumns(columns);
        table.setUniqueIndexGroup(uniqueIndexGroup);
        table.setIndexGroup(indexGroup);
        return table;
    }

    private static void addUniqueIndexByGroupNo(Integer index, Map<Integer, List<Column>> uniqueIndexGroup, Column data) {
        List<Column> indexes = uniqueIndexGroup.get(index) == null ? new ArrayList<>() : uniqueIndexGroup.get(index);
        indexes.add(data);
        uniqueIndexGroup.put(index, indexes);
    }

    private static void addIndexByGroupNo(String index, Map<String, List<Column>> indexGroup, Column data) {
        List<Column> indexes = indexGroup.get(index) == null ? new ArrayList<>() : indexGroup.get(index);
        indexes.add(data);
        indexGroup.put(index, indexes);
    }

    public static boolean isNumber(String str) {
        String reg = "^[0-9]+(.[0-9]+)?$";
        return str.matches(reg);
    }

}
